package cn.com.headfree.salary.dao.impl;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import cn.com.headfree.entity.*;
import cn.com.headfree.salary.dao.IUserDataInfoDao;
import cn.com.headfree.vo.*;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import com.gta.edu.sdk.base.dao.impl.BaseDaoImpl;
import com.gta.edu.sdk.util.StringUtil;

@Repository("userDataInfoDao")
public class UserDataInfoDaoImpl extends BaseDaoImpl<TSalaryInfo> implements IUserDataInfoDao {

    Calendar date = Calendar.getInstance();
    int currentYear = Integer.valueOf(date.get(Calendar.YEAR));
    int lastYear = currentYear - 1;
    String startDate = lastYear + "-01-01";
    String endDate = lastYear + "-12-31";

    //前年
    int beforeYear = lastYear - 1;
    String beforestartDate = beforeYear + "-01-01";
    String beforendDate = beforeYear + "-12-31";

    @Override
    public TEmpBaseInfo getBaseInfoByErpNo(String erpNo) {
        String sql = " select t.*," +
                " GET_DIC('Nation',nation) as nation, " +
                " GET_DIC('Gender',sex) as sex, " +
                " GET_DIC('PoliticalLandscapeText',politi_status) as politi_status " +
                " from t_emp_base_info t where t.erp_no= ?  and  t.is_valid='0' and t.del_sign = '0' ";
        return this.findUnique(TEmpBaseInfo.class, sql, new Object[]{erpNo});
    }

    @Override
    public List<ProTecInfoVo> getProTecInfoByErpNo(String erpNo, String salaryId, String mouId, String ruleId, int year,String orgId) {
        String sql = "SELECT t.id,t.acqu_time,t.subj_name,t.cert_no,h.is_hide, t.post_time," +
                " GET_DIC ( 'ProTechnicalQualifications', t.subj_set ) AS subj_set," +
                " GET_DIC ( 'ProfessionalQualifications', subj_level ) AS subj_level ," +
                " e.point FROM t_pro_tec_info t LEFT JOIN t_salary_handle h ON t.id = h.business_id " +
                " AND h.salary_id = ?" +
                " LEFT JOIN t_rule_element_info e ON t.subj_level = e.element_level" +
                " AND e.project_id = ( SELECT id FROM t_rule_project_info " +
                " WHERE mould_id =? and org_id=?) " +
                " AND e.rule_id =? " +
                " WHERE t.is_valid = '0' AND t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" AND t.post_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(mouId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.post_time desc");
        return this.findAllBySQL(ProTecInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<ProTecInfoVo> getProTecInfoByErpNoInSalary(String erpNo, String salaryId, String mouId, String ruleId, int year,String orgId,String businessTypeId) {
        String sql = "SELECT t.id,t.acqu_time,t.subj_name,t.cert_no,h.is_hide, t.post_time," +
                " GET_DIC ( 'ProTechnicalQualifications', t.subj_set ) AS subj_set," +
                " GET_DIC ( 'ProfessionalQualifications', subj_level ) AS subj_level ," +
                " e.point FROM t_pro_tec_info t LEFT JOIN t_salary_handle h ON t.id = h.business_id " +
                " AND h.salary_id = ?" +
                " LEFT JOIN t_rule_element_info e ON t.subj_level = e.element_level" +
                " AND e.project_id = ( SELECT id FROM t_rule_project_info " +
                " WHERE mould_id =? and org_id=?) " +
                " AND e.rule_id =? " +
                " LEFT JOIN t_salary_business_bind sbb on sbb.item_id = t.id and sbb.salary_id = ? and sbb.business_type_id = ? " +
                " WHERE t.is_valid = '0' AND t.del_sign = '0' and sbb.salary_id is not null ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" AND t.post_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(mouId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(salaryId);
        arryList.add(businessTypeId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.post_time desc");
        return this.findAllBySQL(ProTecInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    /**
     * 根据年份获取专业技术资格
     *
     * @param erpNo
     * @param salaryId
     * @param start
     * @param end
     * @return
     */
    @Override
    public List<ProTecInfoVo> getProTecInfoByErpNo(String erpNo, String salaryId, String mouId, String ruleId, String start, String end,String orgId) {
        String sql = "SELECT t.id,t.acqu_time,t.subj_name,t.cert_no,h.is_hide, t.post_time," +
                " GET_DIC ( 'ProTechnicalQualifications', t.subj_set ) AS subj_set," +
                " GET_DIC ( 'ProfessionalQualifications', subj_level ) AS subj_level ," +
                " e.point FROM t_pro_tec_info t LEFT JOIN t_salary_handle h ON t.id = h.business_id " +
                " AND h.salary_id = ?" +
                " LEFT JOIN t_rule_element_info e ON t.subj_level = e.element_level and e.del_sign = 0" +
                " AND e.project_id = ( SELECT id FROM t_rule_project_info " +
                " WHERE mould_id =? and org_id=? ) " +
                " AND e.rule_id =? " +
                " WHERE t.is_valid = '0' AND t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
//        sqlBuilder.append(" AND t.acqu_time BETWEEN ? and ? ");
        sqlBuilder.append(" AND t.post_time < ? ");
        arryList.add(salaryId);
        arryList.add(mouId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(erpNo);
        arryList.add(start);
//        arryList.add(end);
        sqlBuilder.append(" order by t.post_time desc");
        return this.findAllBySQL(ProTecInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<ProTecInfoVo> getAllProTecInfoByErpNo(String erpNo, String salaryId) {
        String sql = " select t.id,t.acqu_time,t.subj_name,t.cert_no,h.is_hide, t.post_time," +
                " GET_DIC('ProTechnicalQualifications',t.subj_set) as subj_set, " +
                " GET_DIC('ProfessionalQualifications',subj_level) as subj_level " +
                "  from t_pro_tec_info t  " +
                " LEFT JOIN t_salary_handle h on t.id = h.business_id and h.salary_id = ? " +
                " where t.is_valid='0'  and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        sqlBuilder.append(" order by t.post_time desc");
        return this.findAllBySQL(ProTecInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<ProTecInfoVo> getProTecInfoByErpNo(String erpNo, String salaryId, int year) {
        String sql = " select t.id,t.acqu_time,t.subj_name,t.cert_no,h.is_hide, t.post_time," +
                " GET_DIC('ProTechnicalQualifications',t.subj_set) as subj_set, " +
                " GET_DIC('ProfessionalQualifications',subj_level) as subj_level " +
                "  from t_pro_tec_info t  " +
                " LEFT JOIN t_salary_handle h on t.id = h.business_id and h.salary_id = ? " +
                " where t.is_valid='0'  and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" AND t.post_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.post_time, desc");
        return this.findAllBySQL(ProTecInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<SkillLevelInfoVo> getSkillLevelInfoByErpNo(String erpNo, String salaryId, int year) {
        String sql = "  select t.id,t.acqu_time,t.tech_type,t.cert_no,h.is_hide, " +
                " GET_DIC('SkillAppraisalWork',tech_type) as tech_type, " +
                " GET_DIC('QualificationLevel',t.qualifi_level) as qualifi_level " +
                " from t_skill_level_info t  " +
                " LEFT JOIN t_salary_handle h on t.id = h.business_id and h.salary_id = ? " +
                " where t.is_valid='0' and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" AND t.acqu_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.acqu_time desc");
        return this.findAllBySQL(SkillLevelInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<SkillLevelInfoVo> getSkillLevelInfoByErpNo(String erpNo, String salaryId, String mouId, String ruleId, int year,String orgId) {
        String sql = "SELECT t.id,t.acqu_time,t.cert_no,h.is_hide,t.post_time," +
                " GET_DIC ( 'SkillAppraisalWork', tech_type ) AS tech_type," +
                " GET_DIC ( 'QualificationLevel', t.qualifi_level ) AS qualifi_level,e.point" +
                " FROM t_skill_level_info t" +
                " LEFT JOIN t_salary_handle h ON t.id = h.business_id" +
                " AND h.salary_id = ? " +
                " LEFT JOIN t_rule_element_info e ON t.qualifi_level = e.element_level" +
                " AND e.project_id = ( SELECT id FROM t_rule_project_info WHERE mould_id = ? and org_id=? )" +
                " AND e.rule_id = ?	WHERE t.is_valid = '0' AND t.del_sign = '0'";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" AND t.post_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(mouId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.post_time desc");
        return this.findAllBySQL(SkillLevelInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<SkillLevelInfoVo> getSkillLevelInfoByErpNoInSalary(String erpNo, String salaryId, String mouId, String ruleId, int year,String orgId,String businessTypeId) {
        String sql = "SELECT t.id,t.acqu_time,t.cert_no,h.is_hide,t.post_time," +
                " GET_DIC ( 'SkillAppraisalWork', tech_type ) AS tech_type," +
                " GET_DIC ( 'QualificationLevel', t.qualifi_level ) AS qualifi_level,e.point" +
                " FROM t_skill_level_info t" +
                " LEFT JOIN t_salary_handle h ON t.id = h.business_id" +
                " AND h.salary_id = ? " +
                " LEFT JOIN t_rule_element_info e ON t.qualifi_level = e.element_level" +
                " AND e.project_id = ( SELECT id FROM t_rule_project_info WHERE mould_id = ? and org_id=? ) AND e.rule_id = ?	" +
                " LEFT JOIN t_salary_business_bind sbb on sbb.item_id = t.id and sbb.salary_id = ? and sbb.business_type_id = ? " +
                " WHERE t.is_valid = '0' AND t.del_sign = '0' and sbb.salary_id is not null ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" AND t.post_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(mouId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(salaryId);
        arryList.add(businessTypeId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.post_time desc");
        return this.findAllBySQL(SkillLevelInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    /**
     * 获取技能等级用户积分计算
     *
     * @param erpNo
     * @param salaryId
     * @param mouId
     * @param ruleId
     * @param start
     * @param end
     * @return
     */
    @Override
    public List<SkillLevelInfoVo> getSkillLevelInfoByErpNo(String erpNo, String salaryId, String mouId, String ruleId, String start, String end,String orgId) {
        String sql = "SELECT t.id,t.acqu_time,t.cert_no,h.is_hide, t.post_time," +
                " GET_DIC ( 'SkillAppraisalWork', tech_type ) AS tech_type," +
                " GET_DIC ( 'QualificationLevel', t.qualifi_level ) AS qualifi_level,e.point" +
                " FROM t_skill_level_info t" +
                " LEFT JOIN t_salary_handle h ON t.id = h.business_id" +
                " AND h.salary_id = ? " +
                " LEFT JOIN t_rule_element_info e ON t.qualifi_level = e.element_level" +
                " AND e.project_id = ( SELECT id FROM t_rule_project_info WHERE mould_id = ? and org_id=? )" +
                " AND e.rule_id = ?	WHERE t.is_valid = '0' AND t.del_sign = '0'";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
//		sqlBuilder.append(" AND t.acqu_time BETWEEN ? and ? ");
        sqlBuilder.append(" AND t.post_time < ? ");
        arryList.add(salaryId);
        arryList.add(mouId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(erpNo);
        arryList.add(start);
//        arryList.add(end);
        sqlBuilder.append(" order by t.post_time desc");
        return this.findAllBySQL(SkillLevelInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<SkillLevelInfoVo> getAllSkillLevelInfoByErpNo(String erpNo, String salaryId) {
        String sql = "  select t.id,t.acqu_time,t.tech_type,t.cert_no,h.is_hide, " +
                " GET_DIC('SkillAppraisalWork',tech_type) as tech_type, " +
                " GET_DIC('QualificationLevel',t.qualifi_level) as qualifi_level " +
                " from t_skill_level_info t  " +
                " LEFT JOIN t_salary_handle h on t.id = h.business_id and h.salary_id = ? " +
                " where t.is_valid='0' and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        sqlBuilder.append(" order by t.acqu_time desc");
        return this.findAllBySQL(SkillLevelInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<ExpertTitleVo> getExpertTitleInfoByErpNo(String erpNo, String salaryId, int year) {
        String sql = "  select t.id,t.acqu_time,t.emp_term_start,t.emp_term_end,h.is_hide, " +
                " GET_DIC('TalentTitle',t.emp_title) as emp_title, " +
                " GET_DIC('ProWorkTypes',t.major) as major, " +
                " GET_DIC('ExpertLevel',t.level_name) as level_name " +
                " from t_expert_title_info t " +
                " LEFT JOIN t_salary_handle h on t.id = h.business_id and h.salary_id = ? " +
                " where t.is_valid='0' and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" AND t.acqu_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.acqu_time desc");
        return this.findAllBySQL(ExpertTitleVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<ExpertTitleVo> getExpertTitleInfoByErpNo(String erpNo, String salaryId, String mouldId, String ruleId, int year,String orgId) {
        String sql = "  select t.id,t.acqu_time,t.emp_term_start,t.emp_term_end,h.is_hide,e.point,t.post_time, " +
                " GET_DIC('TalentTitle',t.emp_title) as emp_title, " +
                " GET_DIC('ProWorkTypes',t.major) as major, " +
                " GET_DIC('ExpertLevel',t.level_name) as level_name " +
                " from t_expert_title_info t " +
                " LEFT JOIN t_salary_handle h on t.id = h.business_id and h.salary_id = ? " +
                " LEFT JOIN t_rule_element_info e on t.level_name = e.element_type and t.emp_title = e.element_level and e.project_id = (select id from t_rule_project_info where mould_id =? and org_id=? ) and e.rule_id =? " +
                " where t.is_valid='0' and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" AND t.post_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by e.point desc");
        return this.findAllBySQL(ExpertTitleVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<ExpertTitleVo> getExpertTitleInfoByErpNoInSalary(String erpNo, String salaryId, String mouldId, String ruleId, int year,String orgId,String businessTypeId) {
        String sql = "  select t.id,t.acqu_time,t.emp_term_start,t.emp_term_end,h.is_hide,e.point, t.post_time," +
                " GET_DIC('TalentTitle',t.emp_title) as emp_title, " +
                " GET_DIC('ProWorkTypes',t.major) as major, " +
                " GET_DIC('ExpertLevel',t.level_name) as level_name " +
                " from t_expert_title_info t " +
                " LEFT JOIN t_salary_handle h on t.id = h.business_id and h.salary_id = ? " +
                " LEFT JOIN t_rule_element_info e on t.level_name = e.element_type and t.emp_title = e.element_level and e.project_id = (select id from t_rule_project_info where mould_id =? and org_id=? ) and e.rule_id =? " +
                " LEFT JOIN t_salary_business_bind sbb on sbb.item_id = t.id and sbb.salary_id = ? and sbb.business_type_id = ? " +
                " where t.is_valid='0' and t.del_sign = '0' and sbb.salary_id is not null ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" AND t.post_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(salaryId);
        arryList.add(businessTypeId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by e.point desc");
        return this.findAllBySQL(ExpertTitleVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<ExpertTitleVo> getFiveYearExpertTitleInfoByErpNo(String erpNo, String salaryId, String mouldId, String ruleId, int year,String orgId) {
        String sql = "  select t.id,t.acqu_time,t.emp_term_start,t.emp_term_end,h.is_hide,e.point,t.post_time, " +
                " GET_DIC('TalentTitle',t.emp_title) as emp_title, " +
                " GET_DIC('ProWorkTypes',t.major) as major, " +
                " GET_DIC('ExpertLevel',t.level_name) as level_name " +
                " from t_expert_title_info t " +
                " LEFT JOIN t_salary_handle h on t.id = h.business_id and h.salary_id = ? " +
                " LEFT JOIN t_rule_element_info e on t.level_name = e.element_type and t.emp_title = e.element_level and e.del_sign = 0 and e.project_id = (select id from t_rule_project_info where mould_id =? and org_id=? ) and e.rule_id =? " +
                " where t.is_valid='0' and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        int endYear = year - 1;
        int startYear = year - 4;
        String startDate = startYear + "-01-01";
        String endDate = endYear + "-12-31";
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" AND t.post_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(erpNo);
        arryList.add(startDate);
        arryList.add(endDate);
        sqlBuilder.append(" order by t.post_time desc");
        return this.findAllBySQL(ExpertTitleVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<ExpertTitleVo> getAllExpertTitleInfoByErpNo(String erpNo, String salaryId) {
        String sql = "  select t.id,t.acqu_time,t.emp_term_start,t.emp_term_end,h.is_hide, t.post_time," +
                " GET_DIC('TalentTitle',t.emp_title) as emp_title, " +
                " GET_DIC('ProWorkTypes',t.major) as major, " +
                " GET_DIC('ExpertLevel',t.level_name) as level_name " +
                " from t_expert_title_info t " +
                " LEFT JOIN t_salary_handle h on t.id = h.business_id and h.salary_id = ? " +
                " where t.is_valid='0' and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        sqlBuilder.append(" order by t.acqu_time desc");
        return this.findAllBySQL(ExpertTitleVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<AcadeAchiveVo> getAcadeAchiveInfoByErpNo(String erpNo, String salaryId, int year) {
        String sql = " select t.id,t.pub_time,t.acad_name,t.publisher,t.pub_name,t.issn,r.rank,h.is_hide, " +
                " GET_DIC('XueshuchengguoJb',t.level) as pub_type, " +
                " GET_DIC('XueshuchengguoType',t.acad_type) as acad_type, " +
                " GET_DIC('XueshuchengguoJs',r.role) as my_duty, " +
                " t.part_count as total " +
                " from t_acade_achive_info t " +
                " left join t_part_rank r on t.id = r.item_id " +
                " LEFT JOIN t_salary_handle h on h.business_id = t.id and h.salary_id = ? and h.erp_no = ? " +
                " where  r.item_type='12' and t.is_valid='0'and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<Object> arryList = new ArrayList<Object>();
        sqlBuilder.append(" AND r.erp_no= ? ");
        sqlBuilder.append(" AND t.pub_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.pub_time desc");
        return this.findAllBySQL(AcadeAchiveVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<AcadeAchiveVo> getAcadeAchiveInfoByErpNo(String erpNo, String salaryId, String mouldId, String ruleId, int year,String orgId) {
        String sql = " select t.id,t.pub_time,t.acad_name,t.publisher,t.pub_name,t.issn,r.rank,h.is_hide,e.point,r.role, " +
                " GET_DIC('XueshuchengguoJb',t.level) as pub_type, " +
                " GET_DIC('XueshuchengguoType',t.acad_type) as acad_type, " +
                " GET_DIC('XueshuchengguoJs',r.role) as my_duty, " +
                " t.part_count as total " +
                " from t_acade_achive_info t " +
                " left join t_part_rank r on t.id = r.item_id " +
                " LEFT JOIN t_salary_handle h on h.business_id = t.id and h.salary_id = ? and h.erp_no = ? " +
                " LEFT JOIN t_rule_element_info e on t.level = e.element_level and e.del_sign = 0 and e.project_id = (select id from t_rule_project_info where mould_id =? and org_id=? ) and e.rule_id =? " +
                " where  r.item_type='12' and t.is_valid='0'and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<Object> arryList = new ArrayList<Object>();
        sqlBuilder.append(" AND r.erp_no= ? ");
        sqlBuilder.append(" AND t.pub_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.pub_time desc");
        return this.findAllBySQL(AcadeAchiveVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<AcadeAchiveVo> getAcadeAchiveInfoByErpNoInSalary(String erpNo, String salaryId, String mouldId, String ruleId, int year,String orgId ,String businessTypeId) {
        String sql = " select t.id,t.pub_time,t.acad_name,t.publisher,t.pub_name,t.issn,r.rank,h.is_hide,e.point,r.role, " +
                " GET_DIC('XueshuchengguoJb',t.level) as pub_type, " +
                " GET_DIC('XueshuchengguoType',t.acad_type) as acad_type, " +
                " GET_DIC('XueshuchengguoJs',r.role) as my_duty, " +
                " t.part_count as total " +
                " from t_acade_achive_info t " +
                " left join t_part_rank r on t.id = r.item_id " +
                " LEFT JOIN t_salary_handle h on h.business_id = t.id and h.salary_id = ? and h.erp_no = ? " +
                " LEFT JOIN t_rule_element_info e on t.level = e.element_level and e.del_sign = 0 and e.project_id = (select id from t_rule_project_info where mould_id =? and org_id=? ) and e.rule_id =? " +
                " LEFT JOIN t_salary_business_bind sbb on sbb.item_id = t.id and sbb.salary_id = ? and sbb.business_type_id = ? " +
                " where  r.item_type='12' and t.is_valid='0'and t.del_sign = '0' and sbb.salary_id is not null  ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<Object> arryList = new ArrayList<Object>();
        sqlBuilder.append(" AND r.erp_no= ? ");
        sqlBuilder.append(" AND t.pub_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(salaryId);
        arryList.add(businessTypeId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.pub_time desc");
        return this.findAllBySQL(AcadeAchiveVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<PatentInfoVo> getPatentInfoByErpNo(String erpNo, String salaryId, String mouldId, String ruleId, int year,String orgId) {
        String sql = "   select t.id,t.patent_name,t.patent_no,t.decl_date,t.apply_date,t.inventor,r.rank,h.is_hide,e.point, " +
                "  GET_DIC('PatentType',t.patent_type) as patent_type, " +
                " t.part_count as total " +
                "  from t_patent_info t  " +
                " left join t_part_rank r on r.item_id=t.id " +
                " LEFT JOIN t_salary_handle h on h.business_id = t.id and h.salary_id = ? and h.erp_no = ? " +
                " LEFT JOIN t_rule_element_info e on t.patent_type = e.element_level and e.del_sign = 0 and e.project_id = (select id from t_rule_project_info where mould_id = ? and org_id=? ) and e.rule_id = ? " +
                " where r.item_type='14'  and  t.is_valid='0' and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND r.erp_no= ? ");
        sqlBuilder.append(" AND t.decl_date BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.decl_date desc");
        return this.findAllBySQL(PatentInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<PatentInfoVo> getPatentInfoByErpNoInSalary(String erpNo, String salaryId, String mouldId, String ruleId, int year,String orgId ,String businessTypeId) {
        String sql = "   select t.id,t.patent_name,t.patent_no,t.decl_date,t.apply_date,t.inventor,r.rank,h.is_hide,e.point, " +
                "  GET_DIC('PatentType',t.patent_type) as patent_type, " +
                " t.part_count as total , r.user_name as inventor " +
                "  from t_patent_info t  " +
                " left join t_part_rank r on r.item_id=t.id " +
                " LEFT JOIN t_salary_handle h on h.business_id = t.id and h.salary_id = ? and h.erp_no = ? " +
                " LEFT JOIN t_rule_element_info e on t.patent_type = e.element_level and e.del_sign = 0 and e.project_id = (select id from t_rule_project_info where mould_id = ? and org_id=? ) and e.rule_id = ? " +
                " LEFT JOIN t_salary_business_bind sbb on sbb.item_id = t.id and sbb.salary_id = ? and sbb.business_type_id = ? " +
                " where r.item_type='14'  and  t.is_valid='0' and t.del_sign = '0'  and sbb.salary_id is not null  ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND r.erp_no= ? ");
        sqlBuilder.append(" AND t.decl_date BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(salaryId);
        arryList.add(businessTypeId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.decl_date desc");
        return this.findAllBySQL(PatentInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<PatentInfoVo> getPatentInfoByErpNo(String erpNo, String salaryId, int year) {
        String sql = "   select t.id,t.patent_name,t.patent_no,t.decl_date,t.apply_date,t.inventor,r.rank,h.is_hide, " +
                "  GET_DIC('PatentType',t.patent_type) as patent_type, " +
                " t.part_count as total , r.user_name as inventor " +
                "  from t_patent_info t  " +
                " left join t_part_rank r on r.item_id=t.id " +
                " LEFT JOIN t_salary_handle h on h.business_id = t.id and h.salary_id = ? and h.erp_no = ? " +
                " where r.item_type='14'  and  t.is_valid='0' and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND r.erp_no= ? ");
        sqlBuilder.append(" AND t.decl_date BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.decl_date desc");
        return this.findAllBySQL(PatentInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<TecManInnovVo> getTecManInnovByErpNo(String erpNo, String salaryId, int year) {
        String sql = "  select t.id,t.achi_name,t.award_name,t.award_date,t.award_org,r.rank,h.is_hide, " +
                " GET_DIC('KjglAchiveType',t.achi_type) as achi_type, " +
                " GET_DIC('JygexinQcJb',t.award_level) as award_level, " +
                " GET_DIC('JygexinQcDj',t.award_grade) as award_grade, " +
                " t.part_count as total " +
                " from t_tec_man_innov t  " +
                " left join t_part_rank r on r.item_id=t.id " +
                " LEFT JOIN t_salary_handle h on h.business_id = t.id and h.salary_id = ? and h.erp_no = ? " +
                " where r.item_type='16' and  t.is_valid='0' and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<Object> arryList = new ArrayList<Object>();
        sqlBuilder.append(" AND r.erp_no= ? ");
        sqlBuilder.append(" AND t.award_date BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by award_date desc");
        return this.findAllBySQL(TecManInnovVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<TecInnovInfoVo> getTecInnovInfoByErpNo(String erpNo, String salaryId, int year) {
        String sql = "  select t.id,t.achi_name,t.award_name,t.award_date,t.award_org,t.group_name,r.rank,h.is_hide, " +
                " GET_DIC('JygexinQcJb',t.award_level) as award_level, " +
                " GET_DIC('JygexinQcDj',t.award_grade) as award_grade, " +
                " t.part_count as total " +
                " from t_tec_innov_info t  " +
                " left join t_part_rank r on r.item_id=t.id " +
                " LEFT JOIN t_salary_handle h on h.business_id = t.id and h.salary_id = ? and h.erp_no = ? " +
                " where r.item_type='15' and  t.is_valid='0' and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<Object> arryList = new ArrayList<Object>();
        sqlBuilder.append(" AND r.erp_no= ? ");
        sqlBuilder.append(" AND t.award_date BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by award_date desc");
        return this.findAllBySQL(TecInnovInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<HonorDatasVo> getHonnorInfoByErpNo(String erpNo, String salaryId, int year) {
        String sql = " select t.id,t.acqu_time,t.honor_title as honor_name,t.award_org as award_unit,h.is_hide, " +
                " GET_DIC('JygexinQcJb',t.honor_level) as honor_level, " +
                " GET_DIC('RuYuLeiBie',t.honor_type) as honor_type " +
                " from t_honor_info t " +
                " LEFT JOIN t_salary_handle h on t.id = h.business_id and h.salary_id = ? " +
                " where t.is_valid='0'  and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<Object> arryList = new ArrayList<Object>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" AND t.acqu_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.acqu_time desc");
        return this.findAllBySQL(HonorDatasVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<WorkExpInfoVo> getWorkExpInfoByErpNo(String erpNo, String salaryId) {
        String sql = "  select t.id,t.start_date as start_time,t.end_date as end_time,t.company,t.dept,t.post,t.job_content,h.is_hide, " +
                "  GET_DIC('DutyLevel',t.rank) as rank " +
                " from t_work_exp_info t " +
                " LEFT JOIN t_salary_handle h on t.id = h.business_id and h.salary_id = ? " +
                " where  t.is_valid='0' and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        sqlBuilder.append(" order by t.start_date desc");
        return this.findAllBySQL(WorkExpInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<EducateInfoVo> getEducateInfoByErpNo(String erpNo, String salaryId, int year) {
        String sql = " select t.id,t.acqu_time,t.grad_school,t.major,t.is_employ_educ,h.is_hide, " +
                "  GET_DIC('PersonnelQualifications',t.educ_name) as educ_name, t.educ_name as educ_code, " +
                "  GET_DIC('AcademicDegree',t.degree_name) as degree_name,t.degree_name as degree_code," +
                "  GET_DIC('CollegeCategory',t.school_type) as school_type " +
                " from t_educate_info t " +
                " LEFT JOIN t_salary_handle h on t.id = h.business_id and h.salary_id = ? " +
                " where t.is_valid='0' and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" AND t.acqu_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.acqu_time desc");
        return this.findAllBySQL(EducateInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }
    @Override
    public List<EducateInfoVo> getEducateInfoByErpNoInSalary(String erpNo, String salaryId, int year,String businessTypeId) {
        String sql = " select t.id,t.acqu_time,t.grad_school,t.major,t.is_employ_educ,h.is_hide, " +
                "  GET_DIC('PersonnelQualifications',t.educ_name) as educ_name, t.educ_name as educ_code, " +
                "  GET_DIC('AcademicDegree',t.degree_name) as degree_name,t.degree_name as degree_code," +
                "  GET_DIC('CollegeCategory',t.school_type) as school_type " +
                " from t_educate_info t " +
                " LEFT JOIN t_salary_handle h on t.id = h.business_id and h.salary_id = ? " +
                " LEFT JOIN t_salary_business_bind sbb on sbb.item_id = t.id and sbb.salary_id = ? and sbb.business_type_id = ? " +
                " where t.is_valid='0' and t.del_sign = '0' and sbb.salary_id is not null ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" AND t.acqu_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(salaryId);
        arryList.add(businessTypeId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.acqu_time desc");
        return this.findAllBySQL(EducateInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<EducateInfoVo> getEducateInfoByErpNo(String erpNo, String salaryId, String start, String end) {
        String sql = " select t.id,t.acqu_time,t.grad_school,t.major,t.is_employ_educ,h.is_hide, " +
                "  GET_DIC('PersonnelQualifications',t.educ_name) as educ_name, t.educ_name as educ_code, " +
                "  GET_DIC('AcademicDegree',t.degree_name) as degree_name,t.degree_name as degree_code," +
                "  GET_DIC('CollegeCategory',t.school_type) as school_type " +
                " from t_educate_info t " +
                " LEFT JOIN t_salary_handle h on t.id = h.business_id and h.salary_id = ? " +
                " where t.is_valid='0' and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
//		sqlBuilder.append(" AND t.acqu_time BETWEEN ? and ? ");
        sqlBuilder.append(" AND t.acqu_time < ? "); //将时间放开，获取当前年度之前获得学历
        arryList.add(salaryId);
        arryList.add(erpNo);
        //arryList.add(start);
        arryList.add(end);
        sqlBuilder.append(" order by t.acqu_time desc");
        return this.findAllBySQL(EducateInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }


    @Override
    public List<TPartRank> getTPartRankByMids(List<String> mainIds, String type) {
        if (mainIds.isEmpty() || StringUtil.isNullOrEmpty(type)) {
            return null;
        }
        String idsStr = "";
        for (int i = 0; i < mainIds.size(); i++) {
            idsStr += ",'" + mainIds.get(i) + "'";
        }
        idsStr = idsStr.substring(1);
        String jsCode = "";
        if (type.equals("17")) {
            jsCode = "XiangMuKeTiJS";
        } else {
            jsCode = "XueshuchengguoJs";
        }
        String sql = " select t.* ,GET_DIC('" + jsCode + "',t.role) as role_name from t_part_rank t  ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        sqlBuilder.append(" where item_id in (").append(idsStr).append(") and item_type='").append(type).append("' order by item_type, rank ");
        return this.findAllBySQL(TPartRank.class, sqlBuilder.toString(), null, false, null);

    }

    @Override
    public List<EducateInfoVo> getEducateInfoHighEducByErpNo(String erpNo) {
        String sql = " select t.id,t.acqu_time,t.grad_school,t.major,t.is_employ_educ, " +
                "  GET_DIC('PersonnelQualifications',t.educ_name) as educ_name, " +
                "  GET_DIC('AcademicDegree',t.degree_name) as degree_name, " +
                "  GET_DIC('CollegeCategory',t.school_type) as school_type " +
                " from t_educate_info t " +
                " where t.is_valid='0' and t.del_sign = '0' and t.is_highest_educ = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" order by t.acqu_time desc");
        List<String> arryList = new ArrayList<String>();
        arryList.add(erpNo);
        return this.findAllBySQL(EducateInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<ContestInfoVo> getContestInfoByErpNo(String erpNo, String salaryId, int year) {
        String sql = "  select t.id,t.award_date,t.comp_name,t.host_org,t.award_name,h.is_hide, " +
                " GET_DIC('JygexinQcDj',t.comp_rank) as comp_rank, " +
                " GET_DIC('JygexinQcJb',t.award_level) as award_level " +
                " from t_contest_info t  " +
                " LEFT JOIN t_salary_handle h on t.id = h.business_id and h.salary_id = ? " +
                " where t.is_valid='0' and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" AND t.award_date BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.award_date desc");
        return this.findAllBySQL(ContestInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<YearEffectVo> getYearEffectByErpNo(String erpNo, String mouldId, String ruleId, int year,String orgId) {
        StringBuilder sqlBuilder = new StringBuilder("select t.id,t.rate_year as year,GET_DIC('PerformLevelRecentThreeYears',perfor_grade) as perfor_grade,e.point from t_year_effect_info t ");
        sqlBuilder.append(" LEFT JOIN t_rule_element_info e on t.perfor_grade = e.element_level and e.del_sign = 0 and e.project_id = (select id from t_rule_project_info where mould_id =? and org_id=? ) and e.rule_id =? ");
        sqlBuilder.append(" where t.is_valid='0' and t.del_sign = '0' ");
        List<Object> arryList = new ArrayList<Object>();
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        sqlBuilder.append(" AND t.rate_year = ? ");
        arryList.add(year);
        sqlBuilder.append(" AND t.erp_no = ?");
        arryList.add(erpNo);
        return this.findAllBySQL(YearEffectVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<YearEffectVo> getYearEffectByErpNo(String erpNo, int year) {
        StringBuilder sqlBuilder = new StringBuilder("select t.id,t.rate_year as year,GET_DIC('PerformLevelRecentThreeYears',perfor_grade) as perfor_grade from t_year_effect_info t ");
        sqlBuilder.append(" where t.is_valid='0' and t.del_sign = '0' ");
        List<Object> arryList = new ArrayList<Object>();
        sqlBuilder.append(" AND t.rate_year = ? ");
        arryList.add(year);
        sqlBuilder.append(" AND t.erp_no = ?");
        arryList.add(erpNo);
        return this.findAllBySQL(YearEffectVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<EducateInfoVo> getFullTimeEducateInfoByErpNo(String erpNo) {
        String sql = " select t.id,t.acqu_time,t.grad_school,t.major,t.is_employ_educ, " +
                "  GET_DIC('PersonnelQualifications',t.educ_name) as educ_name, " +
                "  GET_DIC('AcademicDegree',t.degree_name) as degree_name, " +
                "  GET_DIC('CollegeCategory',t.school_type) as school_type " +
                " from t_educate_info t " +
                " where t.is_valid='0' and t.del_sign = '0' and t.is_cont_educ = '0' and t.is_employ_educ = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        arryList.add(erpNo);
        sqlBuilder.append(" order by t.acqu_time desc");
        return this.findAllBySQL(EducateInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<HonorDatasVo> getHonnorInfoByErpNo(String erpNo, String salaryId, String mouldId, String ruleId, int year,String orgId) {
        String sql = " select t.id,t.acqu_time,t.honor_title as honor_name,t.award_org as award_unit,h.is_hide,e.point, " +
                " GET_DIC('JygexinQcJb',t.honor_level) as honor_level, " +
                " GET_DIC('RuYuLeiBie',t.honor_type) as honor_type " +
                " from t_honor_info t " +
                " LEFT JOIN t_salary_handle h on h.business_id = t.id and h.salary_id = ? and h.erp_no = ? " +
                " LEFT JOIN t_rule_element_info e on t.honor_type=e.element_type and t.honor_level = e.element_level and e.del_sign = 0 and e.project_id = (select id from t_rule_project_info where mould_id =? and org_id=? ) and e.rule_id =? " +
                " where t.is_valid='0'and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<Object> arryList = new ArrayList<Object>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" AND t.acqu_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.acqu_time desc");
        return this.findAllBySQL(HonorDatasVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<HonorDatasVo> getHonnorInfoByErpNoInSalary(String erpNo, String salaryId, String mouldId, String ruleId, int year,String orgId ,String businessTypeId) {
        String sql = " select t.id,t.acqu_time,t.honor_title as honor_name,t.award_org as award_unit,h.is_hide,e.point, " +
                " GET_DIC('JygexinQcJb',t.honor_level) as honor_level, " +
                " GET_DIC('RuYuLeiBie',t.honor_type) as honor_type " +
                " from t_honor_info t " +
                " LEFT JOIN t_salary_handle h on h.business_id = t.id and h.salary_id = ? and h.erp_no = ? " +
                " LEFT JOIN t_rule_element_info e on t.honor_type=e.element_type and t.honor_level = e.element_level and e.del_sign = 0 and e.project_id = (select id from t_rule_project_info where mould_id =? and org_id=? ) and e.rule_id =? " +
                " LEFT JOIN t_salary_business_bind sbb on sbb.item_id = t.id and sbb.salary_id = ? and sbb.business_type_id = ? " +
                " where t.is_valid='0'and t.del_sign = '0'  and sbb.salary_id is not null ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<Object> arryList = new ArrayList<Object>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" AND t.acqu_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(salaryId);
        arryList.add(businessTypeId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.acqu_time desc");
        return this.findAllBySQL(HonorDatasVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<ContestInfoVo> getContestInfoByErpNo(String erpNo, String salaryId, String mouldId, String ruleId, int year,String orgId) {
        String sql = "  select t.id,t.award_date,t.comp_name,t.host_org,t.award_name,h.is_hide,e.point, " +
                " GET_DIC('JygexinQcDj',t.comp_rank) as comp_rank, " +
                " GET_DIC('JygexinQcJb',t.award_level) as award_level " +
                " from t_contest_info t  " +
                " LEFT JOIN t_salary_handle h on t.id = h.business_id and h.salary_id = ? and h.erp_no = ? " +
                " LEFT JOIN t_rule_element_info e on t.award_level=e.element_type and t.comp_rank = e.element_level and e.del_sign = 0 and e.project_id = (select id from t_rule_project_info where mould_id =? and org_id=? ) and e.rule_id =? " +
                " where t.is_valid='0' and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" AND t.award_date BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.award_date desc");
        return this.findAllBySQL(ContestInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<ContestInfoVo> getContestInfoByErpNoInSalary(String erpNo, String salaryId, String mouldId, String ruleId, int year,String orgId,String businessTypeId) {
        String sql = "  select t.id,t.award_date,t.comp_name,t.host_org,t.award_name,h.is_hide,e.point, " +
                " GET_DIC('JygexinQcDj',t.comp_rank) as comp_rank, " +
                " GET_DIC('JygexinQcJb',t.award_level) as award_level " +
                " from t_contest_info t  " +
                " LEFT JOIN t_salary_handle h on t.id = h.business_id and h.salary_id = ? and h.erp_no = ? " +
                " LEFT JOIN t_rule_element_info e on t.award_level=e.element_type and t.comp_rank = e.element_level and e.del_sign = 0 and e.project_id = (select id from t_rule_project_info where mould_id =? and org_id=? ) and e.rule_id =? " +
                " LEFT JOIN t_salary_business_bind sbb on sbb.item_id = t.id and sbb.salary_id = ? and sbb.business_type_id = ? " +
                " where t.is_valid='0' and t.del_sign = '0'  and sbb.salary_id is not null  ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" AND t.award_date BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(salaryId);
        arryList.add(businessTypeId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.award_date desc");
        return this.findAllBySQL(ContestInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<TecInnovInfoVo> getTecInnovInfoByErpNo(String erpNo, String salaryId, String mouldId, String ruleId, int year,String orgId) {

        String sql = "  select t.id,t.achi_name,t.award_name,t.award_date,t.award_org,t.group_name,r.rank,h.is_hide,e.point, " +
                " GET_DIC('JygexinQcJb',t.award_level) as award_level, " +
                " GET_DIC('JygexinQcDj',t.award_grade) as award_grade, " +
                " t.part_count as total " +
                " from t_tec_innov_info t  " +
                " left join t_part_rank r on r.item_id=t.id " +
                " LEFT JOIN t_salary_handle h on h.business_id = t.id and h.salary_id = ? and h.erp_no = ? " +
                " LEFT JOIN t_rule_element_info e on t.award_level=e.element_type and t.award_grade = e.element_level and e.del_sign = 0 and e.project_id = (select id from t_rule_project_info where mould_id =? and org_id=?) and e.rule_id =? " +
                " where r.item_type='15' and  t.is_valid='0' and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<Object> arryList = new ArrayList<Object>();
        sqlBuilder.append(" AND r.erp_no= ? ");
        sqlBuilder.append(" AND t.award_date BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.award_date desc");
        return this.findAllBySQL(TecInnovInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<TecInnovInfoVo> getTecInnovInfoByErpNoInSalary(String erpNo, String salaryId, String mouldId, String ruleId, int year,String orgId ,String businessTypeId) {

        String sql = "  select t.id,t.achi_name,t.award_name,t.award_date,t.award_org,t.group_name,r.rank,h.is_hide,e.point, " +
                " GET_DIC('JygexinQcJb',t.award_level) as award_level, " +
                " GET_DIC('JygexinQcDj',t.award_grade) as award_grade, " +
                " t.part_count as total " +
                " from t_tec_innov_info t  " +
                " left join t_part_rank r on r.item_id=t.id " +
                " LEFT JOIN t_salary_handle h on h.business_id = t.id and h.salary_id = ? and h.erp_no = ? " +
                " LEFT JOIN t_rule_element_info e on t.award_level=e.element_type and t.award_grade = e.element_level and e.del_sign = 0 and e.project_id = (select id from t_rule_project_info where mould_id =? and org_id=?) and e.rule_id =? " +
                " LEFT JOIN t_salary_business_bind sbb on sbb.item_id = t.id and sbb.salary_id = ? and sbb.business_type_id = ? " +
                " where r.item_type='15' and  t.is_valid='0' and t.del_sign = '0'  and sbb.salary_id is not null  ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<Object> arryList = new ArrayList<Object>();
        sqlBuilder.append(" AND r.erp_no= ? ");
        sqlBuilder.append(" AND t.award_date BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(salaryId);
        arryList.add(businessTypeId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.award_date desc");
        return this.findAllBySQL(TecInnovInfoVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }


    @Override
    public List<TRuleElementInfo> getTRuleElementInfoByproIdAndMouId(String ruleId, String mouId,String orgId) {
        String sql = " SELECT * FROM t_rule_element_info" +
                " WHERE rule_id=?" +
                " AND del_sign = 0 " +
                " AND project_id=" +
                " (SELECT id FROM t_rule_project_info" +
                " WHERE mould_id=? and org_id=? )" +
                " ORDER BY point DESC";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<Object> arryList = new ArrayList<Object>();
        arryList.add(ruleId);
        arryList.add(mouId);
        arryList.add(orgId);
        return this.findAllBySQL(TRuleElementInfo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<TecManInnovVo> getTecManInnovByErpNo(String erpNo, String salaryId, String mouldId, String ruleId, int year,String orgId) {
        String sql = "  select t.id,t.achi_name,t.award_name,t.award_date,t.award_org,r.rank,h.is_hide,e.point, " +
                " GET_DIC('KjglAchiveType',t.achi_type) as achi_type, " +
                " GET_DIC('JygexinQcJb',t.award_level) as award_level, " +
                " GET_DIC('JygexinQcDj',t.award_grade) as award_grade, " +
                " t.part_count as total " +
                " from t_tec_man_innov t  " +
                " left join t_part_rank r on r.item_id=t.id " +
                " LEFT JOIN t_salary_handle h on h.business_id = t.id and h.salary_id = ? and h.erp_no = ? " +
                " LEFT JOIN t_rule_element_info e on t.award_level=e.element_type and t.award_grade = e.element_level and e.del_sign = 0 and e.project_id = (select id from t_rule_project_info where mould_id =? and org_id=? ) and e.rule_id =? " +
                " where r.item_type='16' and  t.is_valid='0' and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<Object> arryList = new ArrayList<Object>();
        sqlBuilder.append(" AND r.erp_no= ? ");
        sqlBuilder.append(" AND t.award_date BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.award_date desc");
        return this.findAllBySQL(TecManInnovVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<TecManInnovVo> getTecManInnovByErpNoInSalary(String erpNo, String salaryId, String mouldId, String ruleId, int year,String orgId,String businessTypeId) {
        String sql = "  select t.id,t.achi_name,t.award_name,t.award_date,t.award_org,r.rank,h.is_hide,e.point, " +
                " GET_DIC('KjglAchiveType',t.achi_type) as achi_type, " +
                " GET_DIC('JygexinQcJb',t.award_level) as award_level, " +
                " GET_DIC('JygexinQcDj',t.award_grade) as award_grade, " +
                " t.part_count as total " +
                " from t_tec_man_innov t  " +
                " left join t_part_rank r on r.item_id=t.id " +
                " LEFT JOIN t_salary_handle h on h.business_id = t.id and h.salary_id = ? and h.erp_no = ? " +
                " LEFT JOIN t_rule_element_info e on t.award_level=e.element_type and t.award_grade = e.element_level and e.del_sign = 0 and e.project_id = (select id from t_rule_project_info where mould_id =? and org_id=? ) and e.rule_id =? " +
                " LEFT JOIN t_salary_business_bind sbb on sbb.item_id = t.id and sbb.salary_id = ? and sbb.business_type_id = ? " +
                " where r.item_type='16' and  t.is_valid='0' and t.del_sign = '0'  and sbb.salary_id is not null  ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<Object> arryList = new ArrayList<Object>();
        sqlBuilder.append(" AND r.erp_no= ? ");
        sqlBuilder.append(" AND t.award_date BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(salaryId);
        arryList.add(businessTypeId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.award_date desc");
        return this.findAllBySQL(TecManInnovVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

	@Override
	public List<TEmployeeMobilityVo> getEmployeeMobilityByErpNo(String erpNo, String salaryId, String mouldId,
			String ruleId, int year, String orgId) {
		String sql = "SELECT t.id,t.begin_time,t.end_time,h.is_hide," +
                " GET_DIC ( 'FlowMode', t.flow_mode ) AS flow_mode_str," +
                " GET_DIC ( 'FlowArea', t.flow_area ) AS flow_area_str," +
                " GET_DIC ( 'FlowType', t.flow_type ) AS flow_type_str,e.point " +
                " FROM t_employee_mobility t" +
                " LEFT JOIN t_salary_handle h ON t.id = h.business_id " +
                " AND h.salary_id = ? " +
                " LEFT JOIN t_rule_element_info e ON concat(t.flow_type,t.flow_area,t.flow_mode) = e.element_value " +
                " AND e.project_id in ( SELECT id FROM t_rule_project_info WHERE mould_id = ? and org_id=? )" +
                " AND e.rule_id = ?	WHERE t.is_valid = '0' AND t.del_sign = '0'";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
	        sqlBuilder.append(" AND t.erp_no= ? ");
//        sqlBuilder.append(" AND t.begin_time >= ? ");
        arryList.add(salaryId);
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(erpNo);
//        arryList.add(year + "-01-01");
        sqlBuilder.append(" group by t.id ");
        sqlBuilder.append(" order by t.begin_time desc");
        return this.findAllBySQL(TEmployeeMobilityVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
	}

    @Override
    public List<TEmployeeMobilityVo> getEmployeeMobilityByErpNoInSalary(String erpNo, String salaryId, String mouldId,
                                                                String ruleId, int year, String orgId,String businessTypeId) {
        String sql = "SELECT t.id,t.begin_time,t.end_time,h.is_hide," +
                " GET_DIC ( 'FlowMode', t.flow_mode ) AS flow_mode_str," +
                " GET_DIC ( 'FlowArea', t.flow_area ) AS flow_area_str," +
                " GET_DIC ( 'FlowType', t.flow_type ) AS flow_type_str,e.point " +
                " FROM t_employee_mobility t" +
                " LEFT JOIN t_salary_handle h ON t.id = h.business_id " +
                " AND h.salary_id = ? " +
                " LEFT JOIN t_rule_element_info e ON concat(t.flow_type,t.flow_area,t.flow_mode) = e.element_value " +
                " AND e.project_id in ( SELECT id FROM t_rule_project_info WHERE mould_id = ? and org_id=? ) AND e.rule_id = ?	" +
                " LEFT JOIN t_salary_business_bind sbb on sbb.item_id = t.id and sbb.salary_id = ? and sbb.business_type_id = ? " +
                " WHERE t.is_valid = '0' AND t.del_sign = '0'  and sbb.salary_id is not null  ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<String> arryList = new ArrayList<String>();
        sqlBuilder.append(" AND t.erp_no= ? ");
//        sqlBuilder.append(" AND t.begin_time >= ? ");
        arryList.add(salaryId);
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(salaryId);
        arryList.add(businessTypeId);
        arryList.add(erpNo);
//        arryList.add(year + "-01-01");
        sqlBuilder.append(" group by t.id ");
        sqlBuilder.append(" order by t.begin_time desc");
        return this.findAllBySQL(TEmployeeMobilityVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

	@Override
	public List<TEmployeeMobilityVo> getEmployeeMobilityByErpNo(String erpNo, String salaryId, int year) {
		String sql =  "SELECT t.id,t.begin_time,t.end_time,h.is_hide," +
                " GET_DIC ( 'FlowMode', t.flow_mode ) AS flow_mode_str," +
                " GET_DIC ( 'FlowArea', t.flow_area ) AS flow_area_str," +
                " GET_DIC ( 'FlowType', t.flow_type ) AS flow_type_str " +
                " FROM t_employee_mobility t" +
                " LEFT JOIN t_salary_handle h ON t.id = h.business_id " +
                " AND h.salary_id = ? " +
                " where t.is_valid='0'  and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<Object> arryList = new ArrayList<Object>();
        sqlBuilder.append(" AND t.erp_no= ? ");
        sqlBuilder.append(" AND t.begin_time >= ? and t.end_time <=?");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.begin_time desc");
        return this.findAllBySQL(TEmployeeMobilityVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
	}

    @Override
    public List<TecStandardVo> getTecStandardInfoByErpNo(String erpNo,String salaryId,String mouldId,String ruleId,int year,String orgId){
        String sql = "  select t.id,t.standard_name,t.award_date,h.is_hide,e.point,t.standard_type,r.role,t.post_time, " +
                " GET_DIC('tecStandardType',t.standard_type) as standard_type_name, " +
                " t.part_count as total " +
                " from t_tec_standard_info t  " +
                " left join t_part_rank r on r.item_id=t.id " +
                " LEFT JOIN t_salary_handle h on h.business_id = t.id and h.salary_id = ? and h.erp_no = ? " +
                " LEFT JOIN t_rule_element_info e on  t.standard_type = e.element_level and e.del_sign = 0 and e.project_id = (select id from t_rule_project_info where mould_id =? and org_id=? ) and e.rule_id =? " +
                " where r.item_type='21' and  t.is_valid='0' and t.del_sign = '0' ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<Object> arryList = new ArrayList<Object>();
        sqlBuilder.append(" AND r.erp_no= ? ");
        sqlBuilder.append(" AND t.post_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.post_time desc");
        return this.findAllBySQL(TecStandardVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }

    @Override
    public List<TecStandardVo> getTecStandardInfoByErpNoInSalary(String erpNo,String salaryId,String mouldId,String ruleId,int year,String orgId,String businessTypeId){
        String sql = "  select t.id,t.standard_name,t.award_date,h.is_hide,e.point,t.standard_type,r.role,t.post_time, " +
                " GET_DIC('tecStandardType',t.standard_type) as standard_type_name, " +
                " t.part_count as total " +
                " from t_tec_standard_info t  " +
                " left join t_part_rank r on r.item_id=t.id " +
                " LEFT JOIN t_salary_handle h on h.business_id = t.id and h.salary_id = ? and h.erp_no = ? " +
                " LEFT JOIN t_rule_element_info e on  t.standard_type = e.element_level and e.del_sign = 0 and e.project_id = (select id from t_rule_project_info where mould_id =? and org_id=? ) and e.rule_id =? " +
                " LEFT JOIN t_salary_business_bind sbb on sbb.item_id = t.id and sbb.salary_id = ? and sbb.business_type_id = ? " +
                " where r.item_type='21' and  t.is_valid='0' and t.del_sign = '0'  and sbb.salary_id is not null ";
        StringBuilder sqlBuilder = new StringBuilder(sql);
        List<Object> arryList = new ArrayList<Object>();
        sqlBuilder.append(" AND r.erp_no= ? ");
        sqlBuilder.append(" AND t.post_time BETWEEN ? and ? ");
        arryList.add(salaryId);
        arryList.add(erpNo);
        arryList.add(mouldId);
        arryList.add(orgId);
        arryList.add(ruleId);
        arryList.add(salaryId);
        arryList.add(businessTypeId);
        arryList.add(erpNo);
        arryList.add(year + "-01-01");
        arryList.add(year + "-12-31");
        sqlBuilder.append(" order by t.post_time desc");
        return this.findAllBySQL(TecStandardVo.class, sqlBuilder.toString(), null, false, arryList.toArray());
    }
}
